package me.seu.demo.service.sim;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import me.seu.demo.service.operate.POIUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.text.MessageFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * 物联网卡和设备编号绑定关系
 *
 * @author liangfeihu
 * @since 2022/10/21 17:40
 */
@Slf4j
public class SimNoAndDeviceNoBind {

    public static void main(String[] args) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(SimNoAndDeviceNoBind.class.getClassLoader().getResourceAsStream("excel/simNoAndDeviceNo.xlsx"));
        XSSFSheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            throw new RuntimeException("sheet not allow null");
        }
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 2) {
            throw new RuntimeException("no sheet data to handle");
        }
        log.info("excel firstRowNum={} lastRowNum={}", firstRowNum, lastRowNum);

        Map<String, String> simAndDeviceNoMap = new HashMap<>();
        for (int index = 1; index <= lastRowNum; index++) {
        //for (int index = 1; index <= 10; index++) {
            Row dataRow = sheet.getRow(index);
            if (dataRow == null) {
                throw new RuntimeException("excel row is null");
            }

            try {
                Cell cell = dataRow.getCell(1);
                String deviceNo = POIUtil.getCellValue(cell);

                Cell cell2 = dataRow.getCell(3);
                String reportContent = POIUtil.getCellValue(cell2);
                if (StringUtils.isBlank(deviceNo) || StringUtils.isBlank(reportContent)) {
                    break;
                }
                JSONObject jsonObject = JSON.parseObject(reportContent);
                String cardNoFor4G = jsonObject.getString("cardNoFor4G");
                if (StringUtils.isNotBlank(cardNoFor4G)) {
                    simAndDeviceNoMap.put(cardNoFor4G, deviceNo);
                }
            } catch (Exception e) {
                log.error("parse error, index={}", index, e);
            }
        }
        log.info("simAndDeviceNoMap size={} info={}", simAndDeviceNoMap.size(), JSON.toJSONString(simAndDeviceNoMap));

        String sql = "update tcbiz_iot_iotsim.card set deviceNo='{0}' where iccid='{1}' and deleted=0;\n";
        StringBuilder sqlBuilder = new StringBuilder();
        simAndDeviceNoMap.forEach((key, value) -> {
            sqlBuilder.append(sql.replace("{0}", value).replace("{1}", key));
        });
        log.info("sql final str={}", sqlBuilder.toString());
    }

}
